﻿
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;
using System.Data;

namespace JoyeriaProject
{
    public class SimpleDatabaseValues
    {
        public string parameter { get; set; }
        public object value { get; set; }
    }

    public class MultipleDatabaseValues
    {
       public List<SimpleDatabaseValues> row { get; set; } 
    }

    class DatabaseManager
    {
        public OleDbDataAdapter Adapter;
        private DataTable dTable;
        private DataSet DS = new DataSet();

        OleDbCommandBuilder cmdBuilder;
        private OleDbConnection connection = new OleDbConnection();
        private const string ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;" +
                @"Data source= DataBase\" +
                @"database.mdb";

        public DatabaseManager()
        {
            connection.ConnectionString = ConnectionString;
        }
      
        //public string[,] ReadFromDB(string SQL)//Devuelve Matriz Multidimansional
        //{
        //    OleDbCommand cmd = new OleDbCommand(SQL, connection);
        //    OleDbDataReader reader;
        //    reader = cmd.ExecuteReader();
        //    string[,] results;
        //    int row = 0;

        //    while (reader.Read())
        //    {
        //        for (int col = 0; col < reader.FieldCount; col++)
        //            temp += (x > 0 ? "," : "") + reader.GetValue(x).ToString();


        //        results[row, col] = new string[1, reader.FieldCount] { temp };
                
        //        row++;
        //    }
        //    reader.Close();

        //    return results;
        //}

        public string[] ReadFromDB(string SQL)
        {
            connection.Open();
            OleDbCommand cmd = new OleDbCommand(SQL, connection);
            OleDbDataReader reader;
 
            reader = cmd.ExecuteReader();
            List<string> results = new List<string>();


            while (reader.Read())
            {
                string temp = "";
                for (int x = 0; x < reader.FieldCount; x++)
                    temp += (x > 0 ? "~" : "") + reader.GetValue(x).ToString();

                results.Add(temp);
            }

            reader.Close();

            connection.Close();
            //return an array with CSV columns
            return results.ToArray();
        }

        public bool InsertToDB(string SQL, List<SimpleDatabaseValues> values)
        {
            //"Insert Into Contacts (FirstName, LastName) Values (?,?)"
            try
            {
                connection.Open();

                OleDbCommand cmd = new OleDbCommand(SQL, connection);

                cmd.CommandType = CommandType.Text;

                foreach (var data in values)
                    cmd.Parameters.AddWithValue(data.parameter, data.value);

                cmd.ExecuteNonQuery();

            }
            catch (Exception ex)
            {
                connection.Close();
                return false;
            }
            connection.Close();
            return true;
        }

        public bool InsertToDBM(string SQL, List<List<SimpleDatabaseValues>> values)
        {
            //"INSERT INTO [Setting] ([Key], [Value]) VALUES (@key, @value);"

            using (OleDbTransaction Trans = connection.BeginTransaction())
            {
                using (OleDbCommand cmd = connection.CreateCommand())
                {
                    cmd.Transaction = Trans;
                    cmd.CommandText = SQL;
                    cmd.Connection = connection;
                    cmd.CommandType = CommandType.Text;
                    try
                    {
                            foreach (List<SimpleDatabaseValues> list in values)
                            {
                                foreach (SimpleDatabaseValues data in list)
                                {   //SqlDbType posible funcion
                                    cmd.Parameters.AddWithValue(data.parameter, data.value);
                                }
                                cmd.ExecuteNonQuery();
                            }

                        Trans.Commit();
                        return true;
                    }
                    catch (Exception)
                    {
                        Trans.Rollback();
                        return false;
                        throw;
                    }

                }
            }
        }

        //public bool InsertToDBM(List<string> SQL, List<SimpleDatabaseValues> values)
        //{
        //    using (OleDbTransaction Trans = connection.BeginTransaction())
        //    {
        //        using (OleDbCommand cmd = connection.CreateCommand())
        //        {
        //            cmd.Transaction = Trans;
        //            cmd.CommandText = SQL;
        //            cmd.Connection = connection;
        //            cmd.CommandType = CommandType.Text;
        //            try
        //            {
        //                foreach (var data in values)
        //                  cmd.Parameters.AddWithValue(data.parameter, data.value);

        //                    cmd.ExecuteNonQuery();
                        

        //                Trans.Commit();
        //                return true;
        //            }
        //            catch (Exception)
        //            {
        //                Trans.Rollback();
        //                return false;
        //                throw;
        //            }

        //        }
        //    }
        //}

        public bool ExecuteToDB(string SQL)
        {
            try
            {
                connection.Open();
                OleDbCommand cmd = new OleDbCommand(SQL, connection);
                cmd.CommandType = CommandType.Text;

                cmd.ExecuteNonQuery();
                connection.Close();
            }
            catch (Exception ex)
            {
                connection.Close();
                return false;
            }

            return true;
        }
        
        public bool UpdateToDB(string SQL, List<SimpleDatabaseValues> values)
        {
            //"Update Limites Set Lunes=@Lunes,Martes=@Martes,Miercoles=@Miercoles,Jueves=@Jueves,Viernes=@Viernes,Sabado=@Sabado,Domingo=@Domingo WHERE ID=@franquicia"
            try
            {
                connection.Open();
                OleDbCommand cmd = new OleDbCommand(SQL, connection);
                cmd.CommandType = CommandType.Text;

                foreach (var data in values)
                    cmd.Parameters.AddWithValue("@" + data.parameter, data.value);

                cmd.ExecuteNonQuery();
                connection.Close();
            }
            catch (Exception ex)
            {
               return false;
            }
           
            return true;
        }

        public SimpleDatabaseValues AddValues(string parameter, object value)
        {
            SimpleDatabaseValues DBvalues = new SimpleDatabaseValues();

            DBvalues.parameter = parameter;
            DBvalues.value = value;

            return DBvalues;
        }
        //ReadOnly
        public DataTable GridSourcer(string SQL)
        {
            //OleDbDataAdapter Adapter = new OleDbDataAdapter(SQL, ConnectionString);
            //OleDbCommandBuilder cmdBuilder = new OleDbCommandBuilder();
            OleDbCommand cmd = new OleDbCommand(SQL, connection);
            OleDbDataReader reader = cmd.ExecuteReader();
            DataTable dTable = new DataTable();

            dTable.Load(reader);
            
            return dTable;
        }

        public DataTable GridSourcer2(string SQL)
        {
              connection.Open();
              Adapter = new OleDbDataAdapter(SQL, ConnectionString);
              cmdBuilder = new OleDbCommandBuilder(Adapter);
              dTable = new DataTable();
              Adapter.Fill(dTable);
           //   connection.Close();
            return dTable;
        }

        public DataSet GridSourcer3(string SQL)
        {
            connection.Open();
            Adapter = new OleDbDataAdapter(SQL, connection);
            Adapter.Fill(DS);
            
            return DS;
        
       }
        public void AddGridChanges( )
        {
            DataSet ds;
            cmdBuilder = new OleDbCommandBuilder(Adapter);
            ds = DS.GetChanges();
            if (ds != null)
            {
                Adapter.Update(DS.Tables[0]);
            }
            DS.AcceptChanges();
        
        }


    ~ DatabaseManager()
    {
        try
        {
            connection.Close();
        }catch(Exception)
        {}
    }
    }
}